FIG. 1 illustrates a conventional relational database system. The system includes an application server 101 communicating with a database server 102. An application 103 at the application server 101 issues requests to the database server 102 using a query language, such as Structured Query Language (SQL). SQL supports cursors, stored procedures, and user defined functions. A cursor is a named control structure used by an application to point to a specific row within some ordered set of rows. A stored procedure is a block of procedural constructs and embedded statements that are stored in a database and can be called by name. A user defined function is defined to the database management system and can be referenced thereafter in SQL queries.
When the application 103 issues a request to the database server 102, the statement is sent with parameters, including a package name, a consistency token, and a section number. The database engine at the database server 102 prepares to execute the statement by creating data structures that represent the package and the section number. The consistency token helps to maintain consistency between the data structures that were created and the application that is being executed. The package name identifies the application source and the statements contained therein. Application sources are programs or sub-programs. A section number is assigned to each unique statement in the package and serves to associate the statement with the corresponding data structures at the database server 102.
However, a problem with the conventional system is section collision, where the section identifier for multiple statements sent over a single database connection is identical. Such section collision results when multiples of the same statement are sent over a single database connection. In this situation, section collision occurs in several ways: (1) multiples of the same statement are sent over a single database connection from different application sources within the same application; (2) a single statement containing multiple application sources is sent; (3) multiples of the same statement from different applications are multiplexed over a single database connection; and (4) multiple open cursor statements for the same cursor are sent over a single database connection.
To illustrate section collision due to multiples of the same statement sent from different application sources within the same application over a single database connection, assume that two different application sources within the same application 103 issues a “CALL SP1” over the same database connection. Both of these statements would have identical package names and section numbers since they contain the same statements.
To illustrate section collision due to a single statement containing multiple application sources being sent over a database connection, assume that the application 103 sends the following statement: “SELECT UDF1(x), UDF2(x), UDF3(x) FROM T1”. If all three UDF's are written in Java, each of these UDF's can be executed in its own JVM environment on an intermediate server which is unaware of any other JVM, and with all three JVM's being multiplexed through a single database connection to the database server 102. Because all three UDF's are executing the same package and statements, each JVM may issue a prepare using an identical section within the package, causing collision with one another.
To illustrate section collision due to multiples of the same statement from different applications multiplexed over a single database connection, assume that a first application issues an “OPEN C1” statement over a connection, and a second application issues an “OPEN C1” statement over the same connection. Both of these statements would have the same package name and section number, resulting in section collision. Conventionally, to avoid this problem, the two statements would be issued using separate threads or connections. This prevents the bandwidth of the connection from being fully utilized.
Section collision due to multiple open cursor statements for the same cursor sent over a single database connection results from the restriction of conventional database systems that there can be only one instance of an open cursor of a specific name at the same processing, or nesting level, within an application. For example, assume that the application 103 issues the statement, “OPEN C1”, to the database server 102 to open the cursor named “C1”. The database server 102 then creates control structures 104 representing the package, the section, and any other needed runtime structures for the statement. But, if the application 103 issues another “OPEN C1” statement, using the same database connection, before the first open cursor closes, then an error occurs. The second open cursor statement would have the same section number as the first open cursor statement. To process both open cursors would result in section collision.
Some database systems would allow the second invocation, but would also close the first open cursor, losing all information from the first open cursor. Other database systems would keep the first cursor information, but would refuse to allow the second cursor to be opened. Still other database systems would use separate threads or remote connections between the application server 101 and the database server 102 to separate the application into multiple processes. This latter approach, however, has a significantly negative impact on performance.
Accordingly, there exists a need for a method and system for uniquely identifying application server requests multiplexed over a single database connection. The present invention addresses such a need.